package cn.com.headfree.deptuser.dao.impl;

import cn.com.headfree.base.entity.TOuterUser;
import cn.com.headfree.deptuser.dao.TOuterUserDao;
import cn.com.headfree.vo.UserDetailVo;
import cn.com.headfree.vo.UserNumVo;
import com.gta.edu.sdk.base.dao.impl.BaseDaoImpl;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.*;

/**
 * Title:
 * Description:
 * Copyright: Copyright (c) 2017 
 * Company: www.headfree.com.cn 
 * @author fengya
 * @version 1.0
 * @date  2019年4月24日 下午8:29:22
 */
@Repository("tOuterUserDao")
public class TOuterUserDaoImpl extends BaseDaoImpl<TOuterUser> implements TOuterUserDao {


	@Override
	public List<UserNumVo> findUserNumByUserType() {
		String sql = "select count(id) as num,user_type from t_outer_user GROUP BY user_type";
		return this.findAllBySQL(UserNumVo.class, sql, null, false, null);
	}

	@Override
	public List<UserDetailVo> getUserList(String userName) {
		StringBuilder sqlBuilder=new StringBuilder();
//		sqlBuilder.append("select u.id as id,u.name as user_name,to_char((d.name)) as dept_names,u.user_type from t_outer_user u,t_outer_dept d,t_outer_dept_user du where u.id=du.user_id and du.dept_id=d.id ");
//		sqlBuilder.append("select u.id as id,u.name as user_name,(LISTAGG(d.name,',') within GROUP (order by d.name)) as dept_names,u.user_type from t_outer_user u,t_outer_dept d,t_outer_dept_user du where u.id=du.user_id and du.dept_id=d.id ");
//		sqlBuilder.append("select u.id as id,u.name as user_name,min(d.id) as dept_id,max(d.name) as dept_names,u.user_type from t_outer_user u,t_outer_dept d,t_outer_dept_user du where u.id=du.user_id and du.dept_id=d.id ");
		sqlBuilder.append("select s.*,DEPT.name as dept_names from (select u.id as id,u.name as user_name,min(d.id) as dept_id,max(d.name) as dept_names,u.user_type from t_outer_user u,t_outer_dept d,t_outer_dept_user du where u.id=du.user_id and du.dept_id=d.id ");
		if(!StringUtils.isBlank(userName)) {
			sqlBuilder.append(" and u.name like '%"+userName+"%' ");
//			sqlBuilder.append(" and LOCATE('"+userName+"',u.name)>0 ");
		}
//		sqlBuilder.append(" group by u.id,U.NAME,U.USER_TYPE");
		sqlBuilder.append(" group by u.id,U.NAME,U.USER_TYPE) s LEFT JOIN t_outer_dept dept on s.dept_id = DEPT.id");
		return this.findAllBySQL(UserDetailVo.class, sqlBuilder.toString(), null, false, null);
	}

	@Override
	public List<UserDetailVo> getUserListByUserType(String userName,String userType) {
		StringBuilder sqlBuilder=new StringBuilder();
		sqlBuilder.append("select s.*,DEPT.name as dept_names from (select u.id as id,u.name as user_name,min(d.id) as dept_id,max(d.name) as dept_names,u.user_type,ding.erp_no from t_outer_user u,t_outer_dept d,t_outer_dept_user du,t_ding_user ding where u.id=du.user_id and du.dept_id=d.id and ding.user_id = u.id ");
		if(!StringUtils.isBlank(userName)) {
			sqlBuilder.append(" and u.name like '%"+userName+"%' ");
		}
		if(!StringUtils.isBlank(userType)) {
			if(userType.equals("1")){//非信通员工
				sqlBuilder.append(" and (u.user_type <> '10000' or u.user_type is null)");
			}else{
				sqlBuilder.append(" and u.user_type = '"+userType+"' ");
			}
		}
		sqlBuilder.append(" group by u.id,U.NAME,U.USER_TYPE) s LEFT JOIN t_outer_dept dept on s.dept_id = DEPT.id");
		return this.findAllBySQL(UserDetailVo.class, sqlBuilder.toString(), null, false, null);
	}

	@Override
	public List<TOuterUser> findAll() {
		String sql = "select u.*,d.erp_no from t_outer_user u " +
				" left join t_ding_user d on d.user_id = u.id";
		return this.findAllBySQL(TOuterUser.class, sql, null, false, null);
	}


}
